Click Here!
home account info subscribe login search My ITKnowledge FAQ/help site map contact us


 
Brief Full
 Advanced
      Search
 Search Tips
To access the contents, click the chapter and section titles.

Oracle Performance Tuning and Optimization
(Publisher: Macmillan Computer Publishing)
Author(s): Edward Whalen
ISBN: 067230886x
Publication Date: 04/01/96

Bookmark It

Search this book:
 
Previous Table of Contents Next


Using ANALYZE with the COMPUTE STATISTICS option scans the entire table, cluster, or index and computes exact statistics. When you compute the exact statistics, the resultant data is more accurate than that achieved by estimating the statistics; however, you use much more system resources to get this information. When you compute statistics for tables and clusters, you must have enough temporary space to load and sort the entire table or cluster. You do not need this temporary space for indexes.

Using ANALYZE To Estimate Statistics

When you use the ANALYZE command to estimate statistics, Oracle does much less work and uses much less temporary space. To run the ANALYZE command to estimate statistics, use one of the following syntaxes.

For Tables:

ANALYZE TABLE table_name
    ESTIMATE STATISTICS;

For Clusters:

ANALYZE CLUSTER cluster_name
    ESTIMATE STATISTICS;

For Indexes:

ANALYZE INDEX index_name
    ESTIMATE STATISTICS;

When you use ANALYZE with the ESTIMATE STATISTICS option, Oracle scans a portion of the table, cluster, or index and computes estimated statistics. You can specify the amount of data scanned and used for statistics by including one or both of these additional parameters:

SAMPLE xxxx ROWS;
SAMPLE yy PERCENT;

Place the SAMPLE xxxx ROWS parameter at the end of the ANALYZE command, as follows:

ANALYZE TABLE table_name
    ESTIMATE STATISTICS
    SAMPLE 10000 ROWS;

Place the SAMPLE yy PERCENT parameter at the end of the ANALYZE command, as follows:

ANALYZE TABLE table_name
    ESTIMATE STATISTICS
    SAMPLE 40 PERCENT;

Although estimating statistics does not give you as accurate a representation as computing statistics does, the lesser amount of resources consumed usually makes estimating statistics a better choice. By making the percentage of data scanned as large as possible for your system, you can increase the effectiveness of the statistics you gather.

Using ANALYZE To Check Structural Integrity

In addition to gather statistics, you can use the ANALYZE command to validate the structure of a table, cluster, or index. You should run this command only if you feel that there is some problem with the structure of these objects. These problems can occur as the result of a hardware or software problem that caused data corruption. By analyzing the structure of the schema objects, you can find any problems immediately and avoid a system crash. The ANALYZE command can be used in this manner with one of the following syntaxes:

For Tables:

ANALYZE TABLE table_name
    VALIDATE STRUCTURE;

For Clusters:

ANALYZE CLUSTER cluster_name
    VALIDATE STRUCTURE;

For Indexes:

ANALYZE INDEX index_name
    VALIDATE STRUCTURE;

Adding the CASCADE option to the ANALYZE command results in the structure of all related tables being analyzed as well. Use the following syntax:

ANALYZE TABLE table_name
    VALIDATE STRUCTURE CASCADE;

When you analyze the integrity of the structure of tables, clusters, or indexes, the command returns any structural problems. If there are problems with structure of these objects, you should drop the object, re-create it, and reload the data.

Using ANALYZE To Determine Chained Rows

You can also use the ANALYZE command to determine the extent and existence of chained or migrated rows in your table or cluster. The existence of chained or migrated rows (as described in Chapter 10, “Performance Enhancements”), if significant, can cause severe performance degradation and should be corrected. Use the ANALYZE command in this manner with one of the following syntaxes:

For Tables:

ANALYZE TABLE table_name
    LIST CHAINED ROWS INTO chained_rows;

For Clusters:

ANALYZE CLUSTER cluster_name
    LIST CHAINED ROWS INTO chained_rows;

The chained_rows table is a table with the proper structure to hold the information returned from the ANALYZE command. You can easily create the chained_rows table by using the UTLCHAIN.SQL script distributed with Oracle.

Summary of the ANALYZE Command

As you have seen, the ANALYZE command can be quite useful for gathering statistics as well as for analyzing the structural integrity of tables, clusters, and indexes. The ANALYZE command can also be used to determine the existence and extent of chained and migrated rows. By using the ANALYZE command to gather statistics, the effectiveness of the cost-based optimizer can be increased; therefore, performance itself can be increased.

Data Dictionary Statistics

When you use the ANALYZE command to create statistics for the cost-based optimizer to use, these statistics are inserted into some internal Oracle performance tables. These tables can be queried through several views. Although these views provide essentially the same information, depending on the particular view you choose, the scope of the information changes slightly. The following views are prefixed with the following characters:


View Description

USER_ This view contains information about the objects owned by the user.
ALL_ This view contains information about the objects accessible by the user. These are objects owned by the user as well as objects with PUBLIC access.
DBA_ This view contains information on all objects in the system.

These views provide information about different parts of the system, such as tables, clusters, indexes, and columns. Following is a brief list of the views available that contain performance information:

  Table views: USER_TABLES, ALL_TABLES, DBA_TABLES
  Cluster views: USER_CLUSTERS, ALL_CLUSTERS, DBA_CLUSTERS
  Index views: USER_INDEXES, ALL_INDEXES, DBA_INDEXES
  Column views: USER_TAB_COLUMNS, ALL_TAB_COLUMNS, DBA_TAB_COLUMNS


Previous Table of Contents Next


Products |  Contact Us |  About Us |  Privacy  |  Ad Info  |  Home

Use of this site is subject to certain Terms & Conditions, Copyright © 1996-2000 EarthWeb Inc.
All rights reserved. Reproduction whole or in part in any form or medium without express written permission of EarthWeb is prohibited.